package com.b2c.repository.erp;

import com.b2c.entity.result.PagingResponse;
import com.b2c.entity.GoodsCategoryAttributeEntity;
import com.b2c.entity.GoodsCategoryAttributeValueEntity;
import com.b2c.entity.GoodsSpecAttrEntity;
import com.b2c.entity.erp.ErpGoodsBrandEntity;
import com.b2c.entity.erp.ErpGoodsEntity;
import com.b2c.entity.erp.ErpGoodsSpecEntity;
import com.b2c.entity.erp.GoodsCategoryEntity;
import com.b2c.entity.erp.GoodsSpecEntity;
import com.b2c.entity.ErpStockLocationEntity;
import com.b2c.entity.erp.vo.*;
import com.b2c.entity.result.EnumResultVo;
import com.b2c.entity.result.ResultVo;
import com.b2c.repository.Tables;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.util.StringUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 描述：
 * 商品Repository
 *
 * @author qlp
 * @date 2019-03-21 16:08
 */
@Repository
public class ErpGoodsRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    protected int getTotalSize() {
        return jdbcTemplate.queryForObject("SELECT FOUND_ROWS() as row_num;", int.class);
    }

    public PagingResponse<ErpGoodsEntity> getList(Integer pageIndex, Integer pageSize, String goodsNumber,Integer isDelete) {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT SQL_CALC_FOUND_ROWS g.* FROM ").append(Tables.ErpGoods).append(" as g ");
        sb.append(" WHERE 1=1 ");
        List<Object> params = new ArrayList<>();
        if (StringUtils.isEmpty(goodsNumber) == false) {
            sb.append(" AND g.number LIKE (?)");
            params.add("%"+goodsNumber+"%");
        }
        if(isDelete != null){
            sb.append(" AND g.isDelete = ?");
            params.add(isDelete);
        }
        sb.append(" ORDER BY g.id desc LIMIT ?,? ");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        List<ErpGoodsEntity> list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(ErpGoodsEntity.class), params.toArray(new Object[params.size()]));
        Integer totalSize = getTotalSize();

        return new PagingResponse<>(pageIndex, pageSize, totalSize, list);
    }

    /**
     * 查询抖音商品销量统计
     * @param shopId
     * @param pageIndex
     * @param pageSize
     * @param goodsNumber
     * @param startTime
     * @param endTime
     * @return
     */
    public PagingResponse<GoodsSearchShowVo> getDySalesList(Long shopId, Integer pageIndex, Integer pageSize, String goodsNumber, Integer startTime, Integer endTime) {
        StringBuilder sb = new StringBuilder("SELECT SQL_CALC_FOUND_ROWS g.image colorImage,g.number goodsNumber,i.`code` specNumber,SUM(i.total_amount) salePrice, SUM(i.combo_num) as quantity,(SELECT  IFNULL(SUM(currentQty),0)  from erp_goods_stock_info WHERE specNumber=i.`code` and  isDelete=0) currentQty from");
        sb.append(" dc_douyin_orders_items i  LEFT JOIN dc_douyin_orders o ON o.id=i.dc_douyin_orders_id ");
        sb.append(" LEFT JOIN erp_goods_spec s ON s.specNumber=i.`code` LEFT JOIN erp_goods g ON g.id=s.goodsId ");
        sb.append(" WHERE o.shop_id=?  and  (o.order_status=1 OR o.order_status=2  OR o.order_status=3 OR o.order_status=5) ");

        List<Object> params = new ArrayList<>();
        params.add(shopId);
        if (StringUtils.isEmpty(goodsNumber) == false) {
            sb.append(" AND g.number = ?");
            params.add(goodsNumber);
        }
        if (!StringUtils.isEmpty(startTime)) {
            sb.append("AND o.create_time > ? ");
            params.add(startTime);
        }
        if (!StringUtils.isEmpty(endTime)) {
            sb.append("AND o.create_time <= ? ");
            params.add(endTime);
        }
        sb.append(" GROUP BY i.`code` ORDER BY quantity desc LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        List<GoodsSearchShowVo> list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(GoodsSearchShowVo.class), params.toArray(new Object[params.size()]));
        Integer totalSize =1000;

        return new PagingResponse<>(pageIndex, pageSize, totalSize, list);
    }

    public List<ErpGoodsEntity> getListFor7Day() {
        String sql = "SELECT * FROM erp_goods WHERE DATEDIFF(now(), FROM_UNIXTIME(`createTime`)) <= 7";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(ErpGoodsEntity.class));
    }

    /***
     * 关键词搜索商品(商品编码、规格编码) -- 零售
     * @param number
     * @return
     */
    public List<GoodsSearchShowVo> getGoodsSpecByNumberForSale(String number, int pageIndex, int pageSize) {
        if (pageIndex <= 1) pageIndex = 0;
        else pageIndex = (pageIndex - 1) * pageSize;
        String sql = "SELECT spec.goodsId,g.name,spec.id as specId,spec.specNumber,spec.color_value,spec.size_value,(SELECT IFNULL(SUM(currentQty),0)  from erp_goods_stock_info WHERE specId=spec.id and  isDelete=0) as currentQty " +
                ",g.number as goodsNumber,u.name as unit,(SELECT IFNULL(price,0) from erp_invoice_info WHERE specId= spec.id and price>0 ORDER BY id desc LIMIT 1 ) costPrice,IFNULL(spec.salePrice,g.salePrice) as salePrice,IFNULL(spec.wholesalePrice,g.wholesalePrice) as wholesalePrice " +
                " FROM " + Tables.ErpGoodsSpec + " spec " +
                " LEFT JOIN " + Tables.ErpGoods + " g ON g.id = spec.goodsId ";
        sql += " LEFT JOIN " + Tables.ErpUnit + " u on u.id=g.unitId";
        sql += " WHERE g.number like(?) OR spec.specNumber like (?) ";//OR g.name like (?) OR spec.specName like (?)
        sql += " LIMIT ?,?";
        List<Object> params = new ArrayList<>();
        params.add("%" + number + "%");
        params.add("%" + number + "%");
//        params.add("%" + number + "%");
//        params.add("%" + key + "%");
        params.add(pageIndex);
        params.add(pageSize);
        List<GoodsSearchShowVo> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(GoodsSearchShowVo.class), params.toArray(new Object[params.size()]));
        return list;
    }


    /***
     * 关键词搜索商品(商品编码、规格编码) -- 采购
     * @param number
     * @return
     */
    public List<GoodsSearchShowVo> getGoodsSpecByNumberForPurchase(String number, int pageIndex, int pageSize) {
        if (pageIndex <= 1) pageIndex = 0;
        else pageIndex = (pageIndex - 1) * pageSize;
        String sql = "SELECT spec.goodsId,spec.color_image,g.name,spec.id as specId,spec.specNumber,spec.color_value,spec.style_value,spec.size_value,"+
        "IFNULL((select currentQty from erp_goods_stock_info where specId=spec.id and isDelete=0),0) AS currentQty " +
                ",g.number as goodsNumber,u.name as unit,IFNULL(spec.purPrice,0) as price,IFNULL(g.freight,0) as freight " +
                " FROM " + Tables.ErpGoodsSpec + " spec " +
                " LEFT JOIN " + Tables.ErpGoods + " g ON g.id = spec.goodsId ";
        sql += " LEFT JOIN " + Tables.ErpUnit + " u on u.id=g.unitId";
        sql += " WHERE g.isDelete=0 and (g.number like(?) OR spec.specNumber like (?) ) ";//OR g.name like (?) OR spec.specName like (?)
        sql += " ORDER BY spec.id desc ";
        sql += " LIMIT ?,?";
        List<Object> params = new ArrayList<>();
        params.add("%" + number + "%");
        params.add("%" + number + "%");
//        params.add("%" + number + "%");
//        params.add("%" + key + "%");
        params.add(pageIndex);
        params.add(pageSize);
        List<GoodsSearchShowVo> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(GoodsSearchShowVo.class), params.toArray(new Object[params.size()]));
        return list;
    }



    /**
     * 根据商品编码获取商品list（like搜索）
     *
     * @param number
     * @return
     */
    public List<GoodsSearchByNumberVo> getGoodsByNumber(String number) {
        String sql = "SELECT *  FROM " + Tables.ErpGoods;
        sql += " WHERE isDelete =0 and disable=0 and number like(?) ";
        List<Object> params = new ArrayList<>();
        params.add("%" + number + "%");
        List<GoodsSearchByNumberVo> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(GoodsSearchByNumberVo.class), params.toArray(new Object[params.size()]));
        return list;
    }

    public ErpGoodsEntity getGoodsEntityByNumber(String number) {
        String sql = "SELECT *  FROM " + Tables.ErpGoods;
        sql += " WHERE isDelete =0 and disable=0 and number = ? ";
        List<Object> params = new ArrayList<>();
        params.add(number);
        List<ErpGoodsEntity> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ErpGoodsEntity.class), params.toArray(new Object[params.size()]));
        if (list == null || list.size() == 0) return null;
        else return list.get(0);
    }

    /**
     * 根据goodsId获取商品规格list
     *
     * @param goodsId
     * @return
     */
    public List<ErpGoodsSpecListVo> getSpecByGoodsId(Integer goodsId) {
        String sql = "SELECT id,specName as name,purPrice,specNumber,currentQty,color_value,size_value,style_value,color_image as image FROM " + Tables.ErpGoodsSpec + " WHERE goodsId=?";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ErpGoodsSpecListVo.class), goodsId);
    }

    public List<ErpGoodsSpecEntity> getSpecListByGoodsId(Integer goodsId) {
        String sql = "SELECT *  FROM " + Tables.ErpGoodsSpec + " WHERE goodsId=?";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class), goodsId);
    }

    /**
     * 根据goodsNumber获取商品规格list
     *
     * @param goodsNumber
     * @return
     */
    public PagingResponse<ErpGoodsSpecListVo> getSpecByGoodsNumber(Integer pageIndex,Integer pageSize,String goodsNumber) {
        String sql = "SELECT SQL_CALC_FOUND_ROWS gs.id,g.id as goodsId,g.name,g.number as goodsNumber,gs.specNumber,(SELECT SUM(currentQty) FROM erp_goods_stock_info WHERE specId=gs.id AND isDelete=0) AS currentQty,"+
                "gs.color_value,gs.size_value,gs.style_value,gs.color_image as image " +
                " FROM " + Tables.ErpGoodsSpec + " as gs " +
                " LEFT JOIN " +Tables.ErpGoods+" as g on g.id=gs.goodsId " +
                " WHERE 1=1 ";

        List<Object> params = new ArrayList<>();
        if (StringUtils.isEmpty(goodsNumber) == false) {
            sql += " AND (g.number like ? OR gs.specNumber like ? )";
            params.add("%" + goodsNumber + "%");
            params.add("%" + goodsNumber + "%");
        }
        sql += " ORDER BY gs.color_id asc LIMIT ?,? ";
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        Integer totalSize = getTotalSize();


        var list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ErpGoodsSpecListVo.class), params.toArray(new Object[params.size()]));
        return new PagingResponse<>(pageIndex, pageSize, totalSize, list);
    }

    /**
     * excel批量导入商品
     *
     * @param goodsList
     * @return
     */
    public ResultVo<Integer> goodsExcelBatchAdd(List<GoodsExcelVo> goodsList) {
        int count = 0;
        for (var g : goodsList) {
            //查询仓库
            try {
                jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpStockLocation + " WHERE id=?", new BeanPropertyRowMapper<>(ErpStockLocationEntity.class), g.getLocationId());
            } catch (Exception e) {
                return new ResultVo<>(EnumResultVo.ParamsError, "仓库不存在");
            }

            //查询商品编号是否存在，存在就修改，不存在就新增
            var goodsSQL = "SELECT * FROM " + Tables.ErpGoods + " WHERE number=? LIMIT 1";
            var goods = jdbcTemplate.query(goodsSQL, new BeanPropertyRowMapper<>(ErpGoodsEntity.class), g.getNumber());
            var goodsId = 0;


            if (goods == null || goods.size() == 0) {
                /***********商品不存，添加商品************/
                //查询单位
                int unitId = 0;
                try {
                    var unit = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpUnit + " WHERE name=?", new BeanPropertyRowMapper<>(UnitEntiy.class), g.getUnit());
                    unitId = unit.getId();
                } catch (Exception e) {
                    unitId = 0;
                }

                //新增商品
                //1     添加goods
                String goodsInsertSQL = "INSERT INTO " + Tables.ErpGoods + " " +
                        " (name,number,unitId,unitName,categoryId,locationId,status,disable,isDelete,createTime) " +
                        " VALUE (?,?,?,?,?,?,?,?,?,?)";

                KeyHolder keyHolder = new GeneratedKeyHolder();

                int finalUnitId = unitId;
                jdbcTemplate.update(new PreparedStatementCreator() {
                    @Override
                    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                        PreparedStatement ps = connection.prepareStatement(goodsInsertSQL, Statement.RETURN_GENERATED_KEYS);
                        ps.setString(1, g.getTitle());
                        ps.setString(2, g.getNumber());
                        ps.setInt(3, finalUnitId);
                        ps.setString(4, g.getUnit());
                        ps.setInt(5, g.getCategoryId());
                        ps.setInt(6, g.getLocationId());
                        ps.setInt(7, 1);
                        ps.setInt(8, 0);
                        ps.setInt(9, 0);
                        ps.setLong(10, System.currentTimeMillis() / 1000);
                        return ps;
                    }
                }, keyHolder);

                goodsId = keyHolder.getKey().intValue();


            } else {
                //存在，新增规格
                goodsId = goods.get(0).getId();
            }

            //查询规格是否存在
            var specSQL = "SELECT * FROM " + Tables.ErpGoodsSpec + " WHERE specNumber=? LIMIT 1";
            var specList = jdbcTemplate.query(specSQL, new BeanPropertyRowMapper<>(GoodsSpecEntity.class), g.getSpecNumber());

            if (specList == null || specList.size() == 0) {
                /*********规格不存在，添加规格***********/

                //开始插入商品规格数据
                var specInsertSQL = "INSERT INTO " + Tables.ErpGoodsSpec + " " +
                        "(goodsId,quantity,specNumber,status,lowQty,locationId,disable,isDelete,currentQty,color_value,size_value) " +
                        " VALUE (?,?,?,?,?,?,?,?,?,?,?)";

                KeyHolder specKeyHolder = new GeneratedKeyHolder();
                int finalGoodsId = goodsId;

                jdbcTemplate.update(new PreparedStatementCreator() {
                    @Override
                    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                        PreparedStatement ps = connection.prepareStatement(specInsertSQL, Statement.RETURN_GENERATED_KEYS);
                        ps.setInt(1, finalGoodsId);
                        ps.setInt(2, 0);
                        ps.setString(3, g.getSpecNumber());
                        ps.setInt(4, 1);
                        ps.setInt(5, g.getMixStock());
                        ps.setInt(6, g.getLocationId());
                        ps.setInt(7, 0);
                        ps.setInt(8, 0);
                        ps.setInt(9, 0);
                        ps.setString(10, g.getColor());
                        ps.setString(11, g.getSize());
                        return ps;
                    }
                }, specKeyHolder);
//                Integer specId = specKeyHolder.getKey().intValue();

                count++;
            }

        }
        return new ResultVo<>(EnumResultVo.SUCCESS, count);
    }


    /**
     * 根据商品规格编码获取规格信息
     *
     * @param number
     * @return
     */
    public GoodsSpecDetailVo getGoodsSpecDetailByNumber(String number) {
        String sql = "SELECT s.id as specId,s.color_value,s.size_value,s.style_value,s.specNumber,s.goodsId,s.currentQty,g.name as goodsName,g.number as goodsNumber " +
                " FROM " + Tables.ErpGoodsSpec + " s ";
        sql += " LEFT JOIN " + Tables.ErpGoods + " g ON g.id=s.goodsId";
        sql += " WHERE s.isDelete =0 and s.disable=0 and s.specNumber = ? ";
        try {
            GoodsSpecDetailVo vo = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(GoodsSpecDetailVo.class), number);
            return vo;
        } catch (Exception e) {
            return null;
        }
    }

    public int getTotalGoods() {
        return jdbcTemplate.queryForObject("SELECT COUNT(0) as row_num FROM " + Tables.ErpGoods + " WHERE disable=0 and isDelete=0 ", int.class);
    }

    /**
     * 新增商品erp goods 单个
     *
     * @param goodsAddVo
     * @return
     */
    @Transactional
    public ResultVo<Integer> goodsAdd(ErpGoodsAddVo goodsAddVo) {
        if (goodsAddVo == null || StringUtils.isEmpty(goodsAddVo.getNumber()) || StringUtils.isEmpty(goodsAddVo.getTitle()))
            return new ResultVo<>(EnumResultVo.ParamsError, "缺少商品基本信息");

        try {
            //查询商品编号是否存在，存在就修改，不存在就新增
            jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpGoods + " WHERE number=? LIMIT 1", new BeanPropertyRowMapper<>(ErpGoodsEntity.class), goodsAddVo.getNumber());
            return new ResultVo<>(EnumResultVo.ParamsError, "商品编码已经存在");
        } catch (Exception e) {
            //不存在
        }
        //添加新商品
        if (goodsAddVo.getSpecList() == null || goodsAddVo.getSpecList().size() == 0){
            ErpGoodsAddSpecVo specVo = new ErpGoodsAddSpecVo();
            specVo.setColorId(0);
            specVo.setColor("");
            specVo.setQuantity(0l);
            specVo.setSizeId(0);
            specVo.setSize("");
            specVo.setHighQty(0);
            specVo.setLowQty(0);
            specVo.setSpecNumber(goodsAddVo.getNumber());
            specVo.setStyle("");
            specVo.setStyleId(0);
            specVo.setImg("");
            goodsAddVo.setSpecList(new ArrayList<>());
            goodsAddVo.getSpecList().add(specVo);
        }
//            return new ResultVo<>(EnumResultVo.ParamsError, "缺少商品规格信息");


        /********1     添加商品erp_goods***********/
        String goodsInsertSQL = "INSERT INTO " + Tables.ErpGoods + " " +
                " (name,number,unitId,categoryId,locationId,status,disable,isDelete,createTime,brand,cost_price,salePrice,image,attr1,attr2,attr3,attr4,attr5,remark,weight,`length`,height,width,width1,width2,width3,erpContactId,freight,sizeImg) " +
                " VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(goodsInsertSQL, Statement.RETURN_GENERATED_KEYS);
                ps.setString(1, goodsAddVo.getTitle());
                ps.setString(2, goodsAddVo.getNumber());
                ps.setInt(3, goodsAddVo.getUnitId());
                ps.setInt(4, goodsAddVo.getCategoryId());
                ps.setInt(5, goodsAddVo.getLocationId());
                ps.setInt(6, 1);
                ps.setInt(7, 0);
                ps.setInt(8, 0);
                ps.setLong(9, System.currentTimeMillis() / 1000);
                ps.setString(10,goodsAddVo.getBrand());
                ps.setDouble(11,goodsAddVo.getCostPrice());
                ps.setDouble(12,goodsAddVo.getSalePrice());
                ps.setString(13,goodsAddVo.getImage());
                ps.setString(14,goodsAddVo.getAttr1());
                ps.setString(15,goodsAddVo.getAttr2());
                ps.setString(16,goodsAddVo.getAttr3());
                ps.setString(17,goodsAddVo.getAttr4());
                ps.setString(18,goodsAddVo.getAttr5());
                ps.setString(19,goodsAddVo.getRemark());
                ps.setFloat(20,goodsAddVo.getWeight());
                ps.setFloat(21,goodsAddVo.getLength());
                ps.setFloat(22,goodsAddVo.getHeight());
                ps.setFloat(23,goodsAddVo.getWidth());
                ps.setFloat(24,goodsAddVo.getWidth1());
                ps.setFloat(25,goodsAddVo.getWidth2());
                ps.setFloat(26,goodsAddVo.getWidth3());
                ps.setInt(27,goodsAddVo.getErpContactId());
                ps.setDouble(28,goodsAddVo.getFreight());
                ps.setString(29, goodsAddVo.getSizeImg());

                return ps;
            }
        }, keyHolder);

        Integer goodsId = keyHolder.getKey().intValue();

        /********2   添加商品规格erp_goods_spec*********/
        var specSQL = "SELECT * FROM " + Tables.ErpGoodsSpec + " WHERE specNumber=? LIMIT 1";
        for (var g : goodsAddVo.getSpecList()) {
            try {
                jdbcTemplate.queryForObject(specSQL, new BeanPropertyRowMapper<>(GoodsSpecEntity.class), g.getSpecNumber());
                //存在，直接退出，并且删除刚才的goods
                jdbcTemplate.update("DELETE FROM " + Tables.ErpGoodsSpec + " WHERE id=?", goodsId);
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                return new ResultVo<>(EnumResultVo.ParamsError, "规格编码已经存在");
            } catch (Exception e) {
                //不存在，插入goods_spec
                var specInsertSQL = "INSERT INTO " + Tables.ErpGoodsSpec + " (goodsId,quantity,specNumber,color_id,color_value,color_image,size_id,size_value,style_id,style_value,status,lowQty,highQty,disable,isDelete,currentQty,purPrice) " +
                        "VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

                KeyHolder specKeyHolder = new GeneratedKeyHolder();

                jdbcTemplate.update(new PreparedStatementCreator() {
                    @Override
                    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                        PreparedStatement ps = connection.prepareStatement(specInsertSQL, Statement.RETURN_GENERATED_KEYS);
                        ps.setInt(1, goodsId);
                        ps.setLong(2, g.getQuantity());
                        ps.setString(3, g.getSpecNumber());
                        ps.setInt(4, g.getColorId());
                        ps.setString(5, g.getColor());
                        ps.setString(6, g.getImg());
                        ps.setInt(7, g.getSizeId());
                        ps.setString(8, g.getSize());
                        ps.setInt(9, g.getStyleId());
                        ps.setString(10, g.getStyle());
                        ps.setInt(11, 0);
                        ps.setInt(12, g.getLowQty());
                        ps.setInt(13, g.getHighQty());
                        ps.setInt(14, 0);
                        ps.setInt(15, 0);
                        ps.setLong(16, g.getQuantity());
                        ps.setDouble(17,(g.getCostPrice() ==null || g.getCostPrice()==0)?goodsAddVo.getCostPrice():g.getCostPrice());
                        return ps;
                    }
                }, specKeyHolder);
                Integer specId = specKeyHolder.getKey().intValue();
            }

        }

        /********3   添加商品规格属性erp_goods_spec_attr*********/
        String specAttrSQL = "INSERT INTO erp_goods_spec_attr (goods_id,type,k,kid,vid,v,img) VALUE (?,?,?,?,?,?,?)";
        if (goodsAddVo.getColors() != null && goodsAddVo.getColors().size() > 0) {
            for (var i : goodsAddVo.getColors()) {
                jdbcTemplate.update(specAttrSQL, goodsId, i.getType(), i.getK(), i.getKid(), i.getVid(), i.getV(), i.getImg());
            }
        }
        if (goodsAddVo.getSizes() != null && goodsAddVo.getSizes().size() > 0) {
            for (var i : goodsAddVo.getSizes()) {
                jdbcTemplate.update(specAttrSQL, goodsId, i.getType(), i.getK(), i.getKid(), i.getVid(), i.getV(), "");
            }
        }
        if (goodsAddVo.getStyles() != null && goodsAddVo.getStyles().size() > 0) {
            for (var i : goodsAddVo.getStyles()) {
                jdbcTemplate.update(specAttrSQL, goodsId, i.getType(), i.getK(), i.getKid(), i.getVid(), i.getV(), "");
            }
        }

        return new ResultVo<>(EnumResultVo.SUCCESS);
    }


    @Transactional
    public ResultVo<Integer> goodsAddSpec(ErpGoodsAddVo goodsAddVo) {

        Integer goodsId = goodsAddVo.getId();

        /********2   添加商品规格erp_goods_spec*********/
        var specSQL = "SELECT * FROM " + Tables.ErpGoodsSpec + " WHERE specNumber=? LIMIT 1";
        for (var g : goodsAddVo.getSpecList()) {
            try {
                jdbcTemplate.queryForObject(specSQL, new BeanPropertyRowMapper<>(GoodsSpecEntity.class), g.getSpecNumber());
                //存在，直接退出，并且删除刚才的goods
                jdbcTemplate.update("DELETE FROM " + Tables.ErpGoodsSpec + " WHERE id=?", goodsId);
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                return new ResultVo<>(EnumResultVo.ParamsError, "规格编码已经存在");
            } catch (Exception e) {
                //不存在，插入goods_spec
                var specInsertSQL = "INSERT INTO " + Tables.ErpGoodsSpec + " (goodsId,quantity,specNumber,color_id,color_value,color_image,size_id,size_value,style_id,style_value,status,lowQty,highQty,disable,isDelete,currentQty) " +
                        "VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

                KeyHolder specKeyHolder = new GeneratedKeyHolder();

                jdbcTemplate.update(new PreparedStatementCreator() {
                    @Override
                    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                        PreparedStatement ps = connection.prepareStatement(specInsertSQL, Statement.RETURN_GENERATED_KEYS);
                        ps.setInt(1, goodsId);
                        ps.setLong(2, g.getQuantity());
                        ps.setString(3, g.getSpecNumber());
                        ps.setInt(4, g.getColorId());
                        ps.setString(5, g.getColor());
                        ps.setString(6, g.getImg());
                        ps.setInt(7, g.getSizeId());
                        ps.setString(8, g.getSize());
                        ps.setInt(9, g.getStyleId());
                        ps.setString(10, g.getStyle());
                        ps.setInt(11, 0);
                        ps.setInt(12, g.getLowQty());
                        ps.setInt(13, g.getHighQty());
                        ps.setInt(14, 0);
                        ps.setInt(15, 0);
                        ps.setLong(16, g.getQuantity());
                        return ps;
                    }
                }, specKeyHolder);
                Integer specId = specKeyHolder.getKey().intValue();
            }

        }

        /********3   添加商品规格属性erp_goods_spec_attr*********/
        String specAttrSQL = "INSERT INTO erp_goods_spec_attr (goods_id,type,k,kid,vid,v,img) VALUE (?,?,?,?,?,?,?)";
        if (goodsAddVo.getColors() != null && goodsAddVo.getColors().size() > 0) {
            for (var i : goodsAddVo.getColors()) {
                jdbcTemplate.update(specAttrSQL, goodsId, i.getType(), i.getK(), i.getKid(), i.getVid(), i.getV(), i.getImg());
            }
        }
        if (goodsAddVo.getSizes() != null && goodsAddVo.getSizes().size() > 0) {
            for (var i : goodsAddVo.getSizes()) {
                jdbcTemplate.update(specAttrSQL, goodsId, i.getType(), i.getK(), i.getKid(), i.getVid(), i.getV(), "");
            }
        }
        return new ResultVo<>(EnumResultVo.SUCCESS);
    }

    /**
     * @param id
     * @return
     */
    public ErpGoodsEntity getById(Integer id) {
        try {
            String sql = "SELECT g.*,gc.`name` as categoryName FROM " + Tables.ErpGoods + " as g LEFT JOIN "+Tables.ErpGoodsCategory +" as gc on gc.id=g.categoryId  WHERE g.id=? ";
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(ErpGoodsEntity.class), id);
        } catch (Exception e) {
            return null;
        }
    }

    /**
     * 根据规格编码获取规格
     *
     * @param specNumber
     * @return
     */
    public ErpGoodsSpecEntity getSpecByNumber(String specNumber) {
        try {
            if(StringUtils.isEmpty(specNumber)) return null;
            String erpSpecSQL="select egs.*,eg.`name` goodTitle,eg.number as goodsNumber,eg.id as goodsId,eg.image as goodsImage  from "+Tables.ErpGoodsSpec+" egs LEFT JOIN "+Tables.ErpGoods+" eg ON egs.goodsId=eg.id where egs.specNumber=?";
            return jdbcTemplate.queryForObject(erpSpecSQL, new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class), specNumber.trim());
        } catch (Exception e) {
            return null;
        }
    }

    public ErpGoodsSpecEntity getSpecBySpecId(Integer specId) {
        try {
            String erpSpecSQL="select egs.*,eg.`name` goodTitle,eg.number as goodsNumber from "+Tables.ErpGoodsSpec+" egs LEFT JOIN "+Tables.ErpGoods+" eg ON egs.goodsId=eg.id where egs.id=?";
            return jdbcTemplate.queryForObject(erpSpecSQL,new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class),specId);
        } catch (Exception e) {
            return null;
        }
    }

    public List<GoodsSpecAttrEntity> getSkuValueList(String type, Integer goodsId) {
        String sql = "SELECT * FROM erp_goods_spec_attr WHERE type=? and goods_id=?";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(GoodsSpecAttrEntity.class), type, goodsId);
    }

    /**
     * 修改erp goods
     *
     * @param goodsId
     * @param goodsAddVo
     * @return
     */
    @Transactional
    public ResultVo<Integer> goodsBaseInfoEdit(Integer goodsId, ErpGoodsAddVo goodsAddVo) {
        if (goodsId == null || goodsId <= 0) return new ResultVo<>(EnumResultVo.ParamsError, "没有需要修改的商品id");
        if (goodsAddVo == null || StringUtils.isEmpty(goodsAddVo.getNumber()) || StringUtils.isEmpty(goodsAddVo.getTitle()))
            return new ResultVo<>(EnumResultVo.ParamsError, "缺少商品基本信息");
       /* if (goodsAddVo.getSpecList() == null || goodsAddVo.getSpecList().size() == 0)
            return new ResultVo<>(EnumResultVo.ParamsError, "缺少商品规格信息");*/

        ErpGoodsEntity goods = null;
        try {
            goods = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpGoods + " WHERE id=?", new BeanPropertyRowMapper<>(ErpGoodsEntity.class), goodsId);
        } catch (Exception e) {
            return new ResultVo<>(EnumResultVo.ParamsError, "没有找到该商品");
        }

        if (goods.getNumber().equals(goodsAddVo.getNumber()) == false) {
            try {
                //查询商品编号是否存在，存在就修改，不存在就新增
                jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpGoods + " WHERE number=? LIMIT 1", new BeanPropertyRowMapper<>(ErpGoodsEntity.class), goodsAddVo.getNumber());

                return new ResultVo<>(EnumResultVo.ParamsError, "商品编码已经存在");
            } catch (Exception e) {
                //不存在
            }
        }

        /********1     修改商品erp_goods***********/
        String goodsSQL = "UPDATE " + Tables.ErpGoods + " SET name=?,number=?,salePrice=?,cost_price=?,attr1=?,attr2=?,attr3=?,attr4=?,attr5=?,remark=?,length=?,height=?,width=?,width1=?,width2=?,width3=?,weight=?,categoryId=?  WHERE id=?";
        jdbcTemplate.update(goodsSQL, goodsAddVo.getTitle(),
                goodsAddVo.getNumber(),
                goodsAddVo.getSalePrice(),goodsAddVo.getCostPrice(),goodsAddVo.getAttr1(),goodsAddVo.getAttr2(),goodsAddVo.getAttr3(),goodsAddVo.getAttr4(),
                goodsAddVo.getAttr5(),goodsAddVo.getRemark(),goodsAddVo.getLength(),goodsAddVo.getHeight(),goodsAddVo.getWidth(),goodsAddVo.getWidth1(),goodsAddVo.getWidth2(),goodsAddVo.getWidth3(),goodsAddVo.getWeight(),
                goodsAddVo.getCategoryId(),goodsId);


        /*        *//********2     修改云购商城 goods number ***********//*
        try {
            var mallGoods = jdbcTemplate.query("SELECT * FROM " + Tables.Goods + " WHERE goods_number=?", new BeanPropertyRowMapper<>(GoodsEntity.class), goods.getNumber());
            if (mallGoods != null && mallGoods.size() > 0) {
                //修改 华衣云购商城 goods number
                for (var g : mallGoods) {
                    jdbcTemplate.update("UPDATE " + Tables.Goods + " SET goods_number=? WHERE id=?", goodsAddVo.getNumber(), g.getId());
                }
            }
        } catch (Exception e) {

        }*/

        /********3   添加商品规格erp_goods_spec*********/

        /********3.1   删除商品规格erp_goods_spec*********/
        /*jdbcTemplate.update("DELETE FROM " + Tables.ErpGoodsSpec + " WHERE goodsId=?", goodsId);*/

//        var specSQL = "SELECT * FROM " + Tables.ErpGoodsSpec + " WHERE specNumber=? LIMIT 1";
/*        for (var g : goodsAddVo.getSpecList()) {
//            try {
//                //存在，更新
//                var spec = jdbcTemplate.queryForObject(specSQL, new BeanPropertyRowMapper<>(GoodsSpecEntity.class), g.getSpecNumber());
//                String specUpdateSQL = "UPDATE " + Tables.ErpGoodsSpec + " SET specNumber=?,color_id=?,color_value=?,color_image=?,size_id=?,size_value=?,style_id=?,style_value=? WHERE id=?";
//                jdbcTemplate.update(specUpdateSQL, g.getSpecNumber(),
//                        g.getColorId(), g.getColor(), "",
//                        g.getSizeId(), g.getSize(),
//                        g.getStyleId(), g.getStyle(),
//                        spec.getId());
//
//            } catch (Exception e) {

            try {
                var specSQL = "SELECT * FROM " + Tables.ErpGoodsSpec + " WHERE specNumber=? LIMIT 1";
                jdbcTemplate.queryForObject(specSQL, new BeanPropertyRowMapper<>(GoodsSpecEntity.class), g.getSpecNumber());
            } catch (Exception e) {
                //不存在，插入goods_spec
                var specInsertSQL = "INSERT INTO " + Tables.ErpGoodsSpec + " (goodsId,quantity,specNumber,color_id,color_value,color_image,size_id,size_value,style_id,style_value,status,lowQty,highQty,disable,isDelete,currentQty) " +
                        "VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

                KeyHolder specKeyHolder = new GeneratedKeyHolder();

                jdbcTemplate.update(new PreparedStatementCreator() {
                    @Override
                    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                        PreparedStatement ps = connection.prepareStatement(specInsertSQL, Statement.RETURN_GENERATED_KEYS);
                        ps.setInt(1, goodsId);
                        ps.setLong(2, g.getQuantity());
                        ps.setString(3, g.getSpecNumber());
                        ps.setInt(4, g.getColorId());
                        ps.setString(5, g.getColor());
                        ps.setString(6, "");
                        ps.setInt(7, g.getSizeId());
                        ps.setString(8, g.getSize());
                        ps.setInt(9, g.getStyleId());
                        ps.setString(10, g.getStyle());
                        ps.setInt(11, 0);
                        ps.setInt(12, g.getLowQty());
                        ps.setInt(13, g.getHighQty());
                        ps.setInt(14, 0);
                        ps.setInt(15, 0);
                        ps.setLong(16, g.getQuantity());
                        return ps;
                    }
                }, specKeyHolder);
                Integer specId = specKeyHolder.getKey().intValue();
            }

        }

        *//********4  添加商品规格属性erp_goods_spec_attr*********//*
        //删除
        jdbcTemplate.update("DELETE FROM erp_goods_spec_attr where goods_id=?", goodsId);

        String specAttrSQL = "INSERT INTO erp_goods_spec_attr (goods_id,type,k,kid,vid,v,img) VALUE (?,?,?,?,?,?,?)";
        if (goodsAddVo.getColors() != null && goodsAddVo.getColors().size() > 0) {
            for (var i : goodsAddVo.getColors()) {
                jdbcTemplate.update(specAttrSQL, goodsId, i.getType(), i.getK(), i.getKid(), i.getVid(), i.getV(), i.getImg());
            }
        }
        if (goodsAddVo.getSizes() != null && goodsAddVo.getSizes().size() > 0) {
            for (var i : goodsAddVo.getSizes()) {
                jdbcTemplate.update(specAttrSQL, goodsId, i.getType(), i.getK(), i.getKid(), i.getVid(), i.getV(), "");
            }
        }
        if (goodsAddVo.getStyles() != null && goodsAddVo.getStyles().size() > 0) {
            for (var i : goodsAddVo.getStyles()) {
                jdbcTemplate.update(specAttrSQL, goodsId, i.getType(), i.getK(), i.getKid(), i.getVid(), i.getV(), "");
            }
        }*/

        return new ResultVo<>(EnumResultVo.SUCCESS);
    }

    /**
     * 根据Id查询商品信息
     *
     * @param id
     * @return
     */
    public ErpGoodsEntity getErpGoodsEntity(Integer id) {
        return jdbcTemplate.queryForObject("SELECT name,locationId,reservoirId,shelfId FROM erp_goods WHERE id=? ", new BeanPropertyRowMapper<>(ErpGoodsEntity.class), id);
    }

    /**
     * 修改商品价格
     *
     * @param goodsId
     * @param wholesalePrice
     * @param salePrice
     */
    public void priceEdit(Integer goodsId, Float wholesalePrice, Float salePrice) {
        StringBuilder sb = new StringBuilder();
        sb.append("UPDATE ").append(Tables.ErpGoods).append(" SET wholesalePrice=?");
        List<Object> params = new ArrayList<>();
        params.add(wholesalePrice);
        if (salePrice != null && salePrice > 0) {
            sb.append(",salePrice=?");
            params.add(salePrice);
        }
        sb.append(" WHERE id=? ");
        params.add(goodsId);
        jdbcTemplate.update(sb.toString(), params.toArray(new Object[params.size()]));

    }

    public List<GoodsCategoryAttributeEntity> getAttributeByCategory(Integer categoryId, int type) {
        String sqlAttr = "SELECT * FROM "+ Tables.ErpGoodsCategoryAttribute + " WHERE type=? and category_id=?";
        var list = jdbcTemplate.query(sqlAttr, new BeanPropertyRowMapper<>(GoodsCategoryAttributeEntity.class), type, categoryId);
        String sqlAttrVal = "SELECT * FROM "+ Tables.ErpGoodsCategoryAttributeValue + " WHERE category_attribute_id=? ORDER BY orderNum DESC";
        for (var item: list) {
            item.setValues(jdbcTemplate.query(sqlAttrVal,new BeanPropertyRowMapper<>(GoodsCategoryAttributeValueEntity.class),item.getId()));
        }
//        String sql = "SELECT * FROM " + Tables.GoodsCategoryAttribute + " WHERE type=? and goods_category_id=?";
//        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(GoodsCategoryAttributeEntity.class), type, categoryId);

        return list;
    }

    public List<ErpGoodsBrandEntity> getBrandList() {
        String sql = "SELECT * FROM erp_goods_brand order by isDefault asc";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(ErpGoodsBrandEntity.class));
    }

    public void updErpGoodSpecAttr(Integer specId,String attr1,String attr2){
        jdbcTemplate.update("update erp_goods_spec set attr1=?,attr2=? where id=?",attr1.replaceAll(" ", ""),attr2.replaceAll(" ", ""),specId);
    }

    /**
     * 获取商品库存数量
     * @param specId
     * @return
     */
    @Transactional
    public Integer getGoodsSpecStockById(Integer specId) {

        String sql= "SELECT IFNULL(SUM(currentQty),0) AS currentQty,IFNULL(SUM(lockedQty),0) AS lockedQty FROM erp_goods_stock_info WHERE specId=? and isDelete=0";
        var stock = jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<>(ErpGoodsRepositorySpecStock.class),specId);

        return stock.getCurrentQty() - stock.getLockedQty();
    }

    public void updateSpecPurPrice(Integer specId, Double purPrice) {
        try {
            jdbcTemplate.update("UPDATE erp_goods_spec SET purPrice=? WHERE id=?", purPrice,specId);
        } catch (Exception e) {
            // TODO: handle exception
            throw e;
        }
        
    }

    public List<GoodsCategoryEntity> getCategoryListByParentId(Integer parendId) {
        String sql = "SELECT * FROM erp_goods_category WHERE parent_id=?";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper(GoodsCategoryEntity.class),parendId);
    }

}
